bash
#!/bin/bash

# 设置Hive环境变量
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive



# 创建表
hive -e "
/* 请假细粒度统计表 */
TRUNCATE TABLE edu_dws.attendance_dws_leave_detail;
DROP TABLE IF EXISTS edu_dws.attendance_dws_leave_detail;
CREATE TABLE edu_dws.attendance_dws_leave_detail(
    leave_date             STRING COMMENT '请假日期',
    class_id               INT COMMENT '班级id',
    student_id             INTEGER COMMENT '学员id',
    is_morning             STRING COMMENT '上午请假 0 没请, 1 请假',
    is_afternoon           STRING COMMENT '下午请假 0 没请, 1 请假',
    student_cnt            INT COMMENT '班级总人数'
    )
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
;

/* 打卡细粒度统计表 */
TRUNCATE TABLE edu_dws.attendance_dws_signin_detail;
DROP TABLE IF EXISTS edu_dws.attendance_dws_signin_detail;
CREATE TABLE edu_dws.attendance_dws_signin_detail(
    class_id               INT COMMENT '班级id',
    student_id             STRING COMMENT '学员id',
    morning_sign           INTEGER COMMENT '早上打卡情况',
    afternoon_sign         STRING COMMENT '下午打卡情况',
    evening_sign           STRING COMMENT '晚上打卡情况',
    signin_date            STRING COMMENT '打卡日期'
)
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
;
"

# 设置Presto环境变量 
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto

使用Presto连接Hive目录并查询
${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
/* 标记早中晚打卡成功的人，其余人皆为异常打卡 */
INSERT INTO hive.edu_dws.attendance_dws_signin_detail
WITH tmp AS (
    SELECT
        *,
-- 早上正常上课时段
        CASE WHEN signin_time >=date_format(DATE_add('minute', -40, date_parse(concat(signin_date, ' ', morning_begin_time),'%Y-%m-%d %H:%i:%s')) ,'%Y-%m-%d %H:%i:%s')
                  AND signin_time <= date_format(date_parse(concat(signin_date, ' ', morning_end_time),'%Y-%m-%d %H:%i:%s') ,'%Y-%m-%d %H:%i:%s')
             THEN '1'
-- 下午正常上课时段
             WHEN signin_time >=date_format(DATE_add('minute', -40, date_parse(concat(signin_date, ' ', afternoon_begin_time),'%Y-%m-%d %H:%i:%s')) ,'%Y-%m-%d %H:%i:%s')
                  AND signin_time <= date_format(date_parse(concat(signin_date, ' ', afternoon_end_time),'%Y-%m-%d %H:%i:%s') ,'%Y-%m-%d %H:%i:%s')
             THEN '2'
-- 晚上正常上课时段
             WHEN signin_time >=date_format(DATE_add('minute', -40, date_parse(concat(signin_date, ' ', evening_begin_time),'%Y-%m-%d %H:%i:%s')) ,'%Y-%m-%d %H:%i:%s')
                  AND signin_time <= date_format(date_parse(concat(signin_date, ' ', evening_end_time),'%Y-%m-%d %H:%i:%s') ,'%Y-%m-%d %H:%i:%s')
             THEN '3'
             END AS time_type,
-- 早上正常上课时打卡情况
        CASE
           WHEN signin_time >=date_format(DATE_add('minute', -40, date_parse(concat(signin_date, ' ', morning_begin_time),'%Y-%m-%d %H:%i:%s')) ,'%Y-%m-%d %H:%i:%s')
               AND signin_time <= date_format(DATE_add('minute', +10, date_parse(concat(signin_date, ' ', morning_begin_time),'%Y-%m-%d %H:%i:%s')) ,'%Y-%m-%d %H:%i:%s') THEN '1'
           WHEN signin_time <= date_format(date_parse(concat(signin_date, ' ', morning_end_time),'%Y-%m-%d %H:%i:%s') ,'%Y-%m-%d %H:%i:%s')
               AND signin_time > date_format(DATE_add('minute', +10, date_parse(concat(signin_date, ' ', morning_begin_time),'%Y-%m-%d %H:%i:%s')) ,'%Y-%m-%d %H:%i:%s') THEN '2'
               END AS morning_sign , --上午正常签到人数和迟到人数
-- 下午正常上课时打卡情况
        CASE
           WHEN signin_time >=date_format(DATE_add('minute', -40, date_parse(concat(signin_date, ' ', afternoon_begin_time),'%Y-%m-%d %H:%i:%s')) ,'%Y-%m-%d %H:%i:%s')
               AND signin_time <= date_format(DATE_add('minute', +10, date_parse(concat(signin_date, ' ', afternoon_begin_time),'%Y-%m-%d %H:%i:%s')) ,'%Y-%m-%d %H:%i:%s') THEN '1'
           WHEN signin_time <= date_format(date_parse(concat(signin_date, ' ', afternoon_end_time),'%Y-%m-%d %H:%i:%s') ,'%Y-%m-%d %H:%i:%s')
               AND signin_time > date_format(DATE_add('minute', +10, date_parse(concat(signin_date, ' ', afternoon_begin_time),'%Y-%m-%d %H:%i:%s')) ,'%Y-%m-%d %H:%i:%s') THEN '2'
               END AS afternoon_sign, --下午正常签到和迟到人数,
-- 晚上正常上课时打卡情况
        CASE
           WHEN normal_class_flag =1 AND
            signin_time >=date_format(DATE_add('minute', -40, date_parse(concat(signin_date, ' ', evening_begin_time),'%Y-%m-%d %H:%i:%s')) ,'%Y-%m-%d %H:%i:%s')
               AND signin_time <= date_format(DATE_add('minute', +10, date_parse(concat(signin_date, ' ', evening_begin_time),'%Y-%m-%d %H:%i:%s')) ,'%Y-%m-%d %H:%i:%s') THEN '1'
           WHEN
               normal_class_flag =1 AND
               signin_time <= date_format(date_parse(concat(signin_date, ' ', evening_end_time),'%Y-%m-%d %H:%i:%s') ,'%Y-%m-%d %H:%i:%s')
               AND signin_time > date_format(DATE_add('minute', +10, date_parse(concat(signin_date, ' ', evening_begin_time),'%Y-%m-%d %H:%i:%s')) ,'%Y-%m-%d %H:%i:%s') THEN '2'
               END AS evening_sign
FROM hive.edu_dwb.attendance_dwb_signin_wide_table
),
tmp_2 AS (
    SELECT
        class_id ,
        class_date ,
        student_id ,
        morning_sign ,
        afternoon_sign ,
        evening_sign ,
        signin_time,
        signin_date,
        ROW_NUMBER () OVER(PARTITION BY signin_date, student_id, time_type) rn
    FROM tmp
    WHERE content IS NOT NULL AND class_date IS NOT NULL AND (morning_sign IS NOT NULL OR afternoon_sign IS NOT NULL OR evening_sign IS NOT NULL )
)
SELECT class_id,
       signin_date,
       student_id,
       morning_sign,
       afternoon_sign,
       evening_sign,
       signin_date AS dt
FROM tmp_2
WHERE rn = 1
;


--------------------------------------------------------------------------------------------------------------------------

/* 过滤出有效请假数据，并标记请假时段 */
INSERT INTO hive.edu_dws.attendance_dws_leave_detail
WITH temp_tb AS
(SELECT
       class_date as leave_date,
       class_id,
       student_id,
       CASE WHEN date_format(date_parse(begin_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')=date_format(date_parse(end_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')
            THEN (IF(begin_time_type=1, '1', '0'))  --当请假起始与结束日期为同一天时，根据起始时间是否为上午判断这一天上午是否请假

            WHEN date_format(date_parse(begin_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')<date_format(date_parse(end_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')
                 AND (class_date=date_format(date_parse(begin_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') OR class_date=date_format(date_parse(end_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d'))
            THEN (IF(begin_time_type=1, '1', '0'))  --当请假日期小于结束日期时，并且上课日期等于起始或结束日期的任意一个，则根据起始时间判断上午是否请假

            WHEN date_format(date_parse(begin_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')<date_format(date_parse(end_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')
                 AND class_date<>date_format(date_parse(begin_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')
                 AND class_date<>date_format(date_parse(end_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')
            THEN '1'  --当上课日期被包含在请假期间中，且不为开始结束中任意一天时，则表示全天请假，所以标记上午请假
       END as is_morning,

       CASE WHEN date_format(date_parse(begin_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')=date_format(date_parse(end_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')
            THEN (IF(end_time_type=2, '1', '0'))

            WHEN date_format(date_parse(begin_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')<date_format(date_parse(end_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')
                 AND (class_date=date_format(date_parse(begin_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') OR class_date=date_format(date_parse(end_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d'))
            THEN (IF(end_time_type=2, '1', '0'))

            WHEN date_format(date_parse(begin_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')<date_format(date_parse(end_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')
                 AND class_date<>date_format(date_parse(begin_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')
                 AND class_date<>date_format(date_parse(end_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d')
            THEN '1'
       END as is_afternoon,
       studying_student_count,
       ROW_NUMBER () OVER(PARTITION BY class_date, class_id, student_id) AS rn
FROM hive.edu_dwb.attendance_dwb_leave_wide_table
-- 过滤无效请假
WHERE (class_date BETWEEN date_format(date_parse(begin_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') AND date_format(date_parse(end_time, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d'))  --剔出非上课时段请假的无效请假情况
       AND (audit_state=1)  --审核通过
       AND (cancel_state=0)) --没撤销
SELECT
       leave_date,
       class_id,
       student_id,
       is_morning,
       is_afternoon,
       studying_student_count,
       leave_date AS dt
FROM temp_tb
WHERE rn = 1
;

"

